﻿using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BatchDelete
{
    static class ExcelUtils
    {
        class CellValue
        {
            public Cell cell;
            public string value;
            public int rowIndex;
            public int colIndex;
        }

        public static async Task<List<string[,]>> LoadData(string path)
        {
            List<string[,]> result = new List<string[,]>();
            //if (stream != null)
            {
                string value = null;
                /*if (System.IO.File.Exists(file.Path))
                {
                    string str = System.IO.File.ReadAllText(file.Path);
                    await (new MessageDialog(str.Length.ToString())).ShowAsync();
                }*/
                //Stream stream = await file.OpenStreamForReadAsync();
                // Open the spreadsheet document for read-only access.
                using (SpreadsheetDocument document = 
                    SpreadsheetDocument.Open(path, false))
                {
                    // Retrieve a reference to the workbook part.
                    WorkbookPart wbPart = document.WorkbookPart;

                    // Find the sheet with the supplied name, and then use that 
                    // Sheet object to retrieve a reference to the first worksheet.
                    Sheet[] sheets = wbPart.Workbook.Descendants<Sheet>().ToArray();
                    for (int k = 0; k < sheets.Length; k++)
                    {
                        List<CellValue> cellValues = new List<CellValue>();
                        string[,] datas;
                        Sheet theSheet = sheets[k];

                        // Throw an exception if there is no sheet.
                        if (theSheet == null)
                        {
                            throw new ArgumentException("sheetName");
                        }

                        // Retrieve a reference to the worksheet part.
                        WorksheetPart wsPart =
                            (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
                        var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                        // Use its Worksheet property to get a reference to the cell 
                        // whose address matches the address you supplied.
                        //Cell theCell = wsPart.Worksheet.Descendants<Cell>().
                        // Where(c => c.CellReference == "A1").FirstOrDefault();
                        List<Row> rows = wsPart.Worksheet.Descendants<Row>().ToList();
                        for (int i = 0; i < rows.Count; i++)
                        {
                            List<Cell> cells = rows[i].Descendants<Cell>().ToList();
                            for (int j = 0; j < cells.Count; j++)
                            {
                                var theCell = cells[j];
                                // If the cell does not exist, return an empty string.
                                if (theCell.InnerText.Length > 0)
                                {
                                    value = theCell.InnerText;
                                    if (theCell.DataType != null)
                                    {
                                        switch (theCell.DataType.Value)
                                        {
                                            case CellValues.SharedString:

                                                // For shared strings, look up the value in the
                                                // shared strings table.
                                                // If the shared string table is missing, something 
                                                // is wrong. Return the index that is in
                                                // the cell. Otherwise, look up the correct text in 
                                                // the table.
                                                if (stringTable != null)
                                                {
                                                    value =
                                                        stringTable.SharedStringTable
                                                        .ElementAt(int.Parse(value)).InnerText;
                                                }
                                                break;

                                            case CellValues.Boolean:
                                                switch (value)
                                                {
                                                    case "0":
                                                        value = "FALSE";
                                                        break;
                                                    default:
                                                        value = "TRUE";
                                                        break;
                                                }
                                                break;
                                        }
                                    }
                                    cellValues.Add(new CellValue() { cell = theCell, value = value, rowIndex = i, colIndex = j });
                                }
                            }
                        }
                        //int row, col;
                        int maxRow = 0, maxCol = 0;
                        foreach (var cellValue in cellValues)
                        {
                            if (cellValue.rowIndex > maxRow)
                                maxRow = cellValue.rowIndex;
                            if (cellValue.colIndex > maxCol)
                                maxCol = cellValue.colIndex;
                        }
                        datas = new string[maxRow + 1, maxCol + 1];
                        foreach (var cellValue in cellValues)
                        {
                            //CellReferenceToIndex(cellValue.cell.CellReference, out row, out col);
                            datas[cellValue.rowIndex, cellValue.colIndex] = cellValue.value;
                        }
                        result.Add(datas);
                    }
                }
            }
            
            return result;
            //await (new MessageDialog("maxRow:" + (maxRow + 1) + ",maxCol:" + (maxCol + 1))).ShowAsync();
        }

        public static String convertNumToColString(int col) {
            int excelColNum = col + 1;
            StringBuilder colRef = new StringBuilder(2);
            int colRemain = excelColNum;

            while(colRemain > 0) {
                int thisPart = colRemain % 26;
                if (thisPart == 0) {
                    thisPart = 26;
                }

                colRemain = (colRemain - thisPart) / 26;
                char colChar = (char)(thisPart + 64);
                colRef.Insert(0, colChar);
            }

            return colRef.ToString();
        }

        public static int convertColStringToIndex(String str) {
            int retval = 0;
            char[] refs = str.ToUpper().ToCharArray();

            for(int k = 0; k < refs.Length; ++k) {
                char thechar = refs[k];
                if (thechar == '$') {
                    if (k != 0) {
                        throw new Exception("Bad col ref format '" + str + "'");
                    }
                } else {
                    retval = retval * 26 + thechar - 65 + 1;
                }
            }

            return retval - 1;
        }
    }
}
